A database management system (DBMS) is a collection of programs that enables one to store, modify, and extract information from a database. There are many different types of DBMSs, ranging from small systems that run locally on personal computers to large systems distributed across a network. DBMSs can differ widely. The terms relational, network, flat, and hierarchical all refer to the way a DBMS organizes information internally. The internal organization can affect how quickly and flexibly one can extract information. Requests for information from a database are made in the form of a query. Data modifications in DBMSs are made through data manipulation language (DML) statements. Such statements allow users to insert, update and delete rows from a table in a database, called the target table. The syntax of the insert statement allows defining the values for the columns of the rows to add to the table. The syntax of the delete statement allows for qualifying the rows to remove from the table. The syntax of update allows for both qualifying rows and specifying new values with which the columns will be modified.
The set of rules for constructing DML statements is known as a query language. Different DBMSs support different query languages, although there is a semi-standardized query language called structured query language (SQL). SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL works with major database programs such as MICROSOFT ACCESS®, DB2®, INFORMIX®, MICROSOFT SQL SERVER®, ORACLE®, and SYBASE®, for example. There are many different versions of the SQL language, but to be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as select, update, delete, insert, where, and others).
Transactions involving data manipulation language (DML) statements may experience some blocking and deadlocking due to reading data in presence of concurrent transactions performing data modifications. As a result, a newer type of isolation level called snapshot is designed to allow transactions to read possibly older but mutually consistent versions of data without blocking other transactions. In this way, snapshot isolation can preserve the consistency of query results while reducing the frequency of blocking and deadlocking. Snapshot isolation has, however, introduced new classes of anomalies that can occur when processing DML statements, that require accessing tables other than the target to validate referential integrity constraints or propagate changes to an indexed view (auxiliary scan), which need to be addressed using novel techniques.
In this regard, there is a need for systems and methods avoiding anomalies that occur due to ensuring read consistency with snapshot isolation in a way that will cause minimal service interruption, delay or impact on system performance.